expdp/impdp是逻辑迁移或者备份使用得比较多的工具，除了大多数常见的参数外，还有一些很实用但是往往了解比较少的参数或功能，

一、expdp参数compression压缩，压缩比还是比较高的

compression有几种选项：

METDATA_ONLY —仅压缩元数据；数据保持原样（在Oracle 数据库 10.2 中同样可用）。

DATA_ONLY —仅压缩数据；元数据保持原样。

ALL —同时压缩元数据和数据。

NONE —此为默认选项；不执行任何压缩。

ora11g@:/home/ora11g> expdp \"/ as sysdba\" directory=expdp dumpfile=full.dmp logfile=full.log full=y compression=all

Export: Release 11.2.0.4.0 - Production onSun Apr 3 18:37:15 2016

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"SYS"."SYS_EXPORT_FULL_01": "/******** AS SYSDBA" directory=expdp dumpfile=full.dmplogfile=full.log full=y compression=all

Estimate in progress using BLOCKS method...

Processing object typeDATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Total estimation using BLOCKS method: 84.43 MB

Processing object type DATABASE_EXPORT/TABLESPACE

Processing object typeDATABASE_EXPORT/PROFILE

Processing object type DATABASE_EXPORT/SYS_USER/USER

......

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA

Processing object type DATABASE_EXPORT/AUDIT

. . exported "HUANG"."T1"                                986.4 KB   86045 rows

. . exported "APEX_030200"."WWV_FLOW_PAGE_PLUGS"         1.040 MB    7416rows

. . exported "APEX_030200"."WWV_FLOW_STEP_ITEMS"         845.3 KB    9671 rows

....

Master table "SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is: /orabak/oracle/full.dmp

Job  "SYS"."SYS_EXPORT_FULL_01" successfully completed at SunApr 3 18:47:07 2016 elapsed 0 00:09:42

ls -l orabak/oracle/full.dmp

-rw-r-----    1 ora11g  dba        43507712 Apr 03 18:47 orabak/oracle/full.dmp

二、数据加密ENCRYPTION

如果客户对数据导出文件安全要求比较高的，可以使用该参数加密，来帮助保护转储文件的安全，在导出数据时使用。

ENCRYPTION

Encrypt part or all of a dump file.

Valid keyword values are: ALL, DATA_ONLY,ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE.

ENCRYPTION_ALGORITHM

Specify how encryption should be done.

Valid keyword values are: [AES128], AES192and AES256.

ENCRYPTION_MODE

Method of generating encryption key.

Valid keyword values are: DUAL, PASSWORDand [TRANSPARENT].

ENCRYPTION_PASSWORD

Password key for creating encrypted datawithin a dump file.

使用expdp加密之前，需要创建wallet

ora11g@:/home/ora11g> expdp \"/ assysdba \" dumpfile=scott.dmp logfile=scott.log directory=EXPDPschemas=scott encryption=data_only encryption_algorithm=aes128

Export: Release 11.2.0.4.0 - Production onMon Apr 4 22:07:43 2016

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORA-39002: invalid operation

ORA-39188: unable to encrypt dump file set

ORA-28365: wallet is not open

创建wallet的文件位置，默认位置为 $ORACLE_BASE/admin/$DB_NAME/wallet，默认情况下钱夹子目录并不存在，需要进行创建。

因此，在我的示例中，该目录为 orabak/oracle/app/admin/abc/wallet。

接下来，执行下面的语句以在钱夹中创建加密密钥：

alter system set encryption key identifiedby "huang123";

该语句同时创建钱夹和密钥。如果现在检查该目录，可以看到刚刚创建的钱夹文件 (ewallet.p12)。

ora11g@:/orabak>ls -lrt

total 8 -rw-r--r--    1 ora11g  dba            2845 Apr 04 22:12 ewallet.p12

钱夹要使用口令才能打开，在本例中，口令为 huang123，该语句也可以打开钱夹。以后无需再创建钱夹了。数据库启动后，只需通过执行以下语句来打开钱夹：

alter system set wallet open identified by "huang123"

expdp \"/ as sysdba \" dumpfile=scott.dmp logfile=scott.log directory=EXPDP schemas=scottencryption=data_only encryption_algorithm=aes128

Export: Release 11.2.0.4.0 - Production onMon Apr 4 22:14:01 2016

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"SYS"."SYS_EXPORT_SCHEMA_02":  "/******** AS SYSDBA"dumpfile=scott.dmp logfile=scott.log directory=EXPDP schemas=scottencryption=data_only encryption_algorithm=aes128

......

. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows

. . exported "SCOTT"."EMP"                               8.570 KB      14 rows

. . exported "SCOTT"."SALGRADE"                          5.867 KB       5 rows

. . exported "SCOTT"."BONUS"                                 0 KB       0 rows

Master table "SYS"."SYS_EXPORT_SCHEMA_02" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_SCHEMA_02 is:/orabak/oracle/scott.dmp

Job "SYS"."SYS_EXPORT_SCHEMA_02" successfully completed at MonApr 4 22:14:42 2016 elapsed 0 00:00:39

三、trace参数

trace参数主要用于调试expdp/impdp错误时用，它的值是由7位16进制表示，前三位表示DataPump组件，后四位一般是0300

生成的日志信息放在BACKGROUND_DUMP_DEST和USER_DUMP_DEST目录下

trace参数值的详细含义：

除了可以在expdp命令行中加入trace参数，比如

expdp abc/abc directory=expdpdumpfile=full.dmp logfile=full.log full=y TRACE=480300

还可以在启动后的Datapump任务追加trace参数，比如

expdp abc/abc ATTACH=sys_export_full_01TRACE=480300

还可以在系统参数级别设置DataPump trace

ALTER SYSTEM SET EVENTS = '39089 trace namecontext forever, level 0x300';

四、status参数

查看DataPump进程的状态，包括并发度，导出模式，文件物理路径等

ora11g:/home/ora11g> expdp \"/ assysdba\" directory=expdp dumpfile=full.dmp logfile=full.log full=y

Export: Release 11.2.0.4.0 - Production onMon Aug 8 18:59:14 2016

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

Starting"SYS"."SYS_EXPORT_FULL_02": "/******** AS SYSDBA" directory=expdp dumpfile=full.dmplogfile=full.log full=y

Estimate in progress using BLOCKS method...

Processing object typeDATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

每秒钟打印一次状态信息，输出到屏幕

Export> status=1

Export> con

Job: SYS_EXPORT_FULL_01Operation: EXPORTMode: FULLState: EXECUTINGBytes Processed: 0

  CurrentParallelism: 1

  JobError Count: 0Dump File: orabak/oracle/full.dmpbytes written: 4,096

Worker 1 Status:Process Name: DW00State: EXECUTINGObject Name: TRCA$INPUT2Object Type: DATABASE_EXPORT/DIRECTORY/DIRECTORYCompleted Objects: 12Total Objects: 12Worker Parallelism: 1

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

五、METRICS参数，

记录每个阶段消耗的时间，在迁移数据的时候非常有用，expdp/impdp都可以设置该参数，

expdp \"/ as sysdba\"directory=expdp dumpfile=full.dmp logfile=full.log full=y content=metadata_onlymetrics=y

Export: Release 11.2.0.4.0 - Production onSun Mar 27 23:14:14 2016

Copyright (c) 1982, 2011, Oracle and/or itsaffiliates.  All rights reserved.

Connected to: Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real ApplicationClusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testingoptions

FLASHBACK automatically enabled to preservedatabase integrity.

Starting "SYS"."SYS_EXPORT_FULL_01":  "/******** AS SYSDBA"directory=expdp dumpfile=full.dmp logfile=full.log full=y content=metadata_onlymetrics=y

Startup took 2 seconds

Processing object type DATABASE_EXPORT/TABLESPACECompleted 5 TABLESPACE objects in 2 seconds

Processing object type DATABASE_EXPORT/PROFILE  Completed 2 PROFILE objects in 0 seconds

Processing object type DATABASE_EXPORT/SYS_USER/USERCompleted 1 USER objects in 0 seconds

Processing object type DATABASE_EXPORT/SCHEMA/USERCompleted 17 USER objects in 0 seconds

Processing object type DATABASE_EXPORT/ROLE

Completed 44 ROLE objects in 0 seconds

Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANTCompleted 5 PROC_SYSTEM_GRANT objects in 1 seconds

......

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCACT_INSTANCECompleted 18 PROCACT_INSTANCE objects in 1 seconds

Processing object type DATABASE_EXPORT/SCHEMA/TABLE/POST_INSTANCE/PROCDEPOBJCompleted 18 PROCDEPOBJ objects in 0 seconds

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCOBJCompleted 6 PROCOBJ objects in 9 seconds

Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMACompleted 4 PROCACT_SCHEMA objects in 6 seconds

Processing object type DATABASE_EXPORT/AUDITCompleted 29 AUDIT objects in 6 seconds

Master table"SYS"."SYS_EXPORT_FULL_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_FULL_01 is:/oracle/expdp/full.dmp

Job "SYS"."SYS_EXPORT_FULL_01" successfully completed at SunMar 27 23:18:31 2016 elapsed 0 00:04:16

六、SQLFILE参数，用于记录导入文件中的所有DDL操作

注意，该参数只能在impdp中使用，并且该impdp动作不会被真正执行，而是把DDL操作都记录在SQLFILE指定的文件中

impdp \"/ as sysdba\"directory=expdp dumpfile=full.dmp logfile=full_in.log full=ycontent=metadata_only SQLFILE=impdp_s.sql

[oracle@expdp]$ cat impdp_s.sql

-- CONNECT SYSTEM

 CREATE USER "HUANG" IDENTIFIED BYVALUES 'S:A519550DF70AA5C64FFD21E2D064E7B17F3EEEEE0038CD2F16226A0411B4;74FBCFB9F163FDDE'DEFAULT TABLESPACE "USERS"TEMPORARY TABLESPACE "TEMP";

-- new object type path:SCHEMA_EXPORT/SYSTEM_GRANT

GRANT UNLIMITED TABLESPACE TO"HUANG";

-- new object type path:SCHEMA_EXPORT/ROLE_GRANT

 GRANT "CONNECT" TO"HUANG";

 GRANT "RESOURCE" TO"HUANG";

-- new object type path:SCHEMA_EXPORT/DEFAULT_ROLE

 ALTER USER "HUANG" DEFAULT ROLE ALL;

-- new object type path:SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT HUANG

......

七、impdp与parallel

如果expdp没有并行导出，impdp导入时加入parallel可以并行导入，并且创建索引时也会使用并行，加快导入速度。

expdp \"/ as sysdba\"directory=expdp dumpfile=aatest.dmp tables=abc.aatest

impdp \"/ as sysdba\"directory=expdp dumpfile=aatest.dmp parallel=2 trace=480300

如果打开sqlfile,可以看到创建索引的并发度为1，这是bug导致，实际是可以使用并发创建索引的

Impdp Parallel Index Creation alwayscreates indexes with degree 1. (Doc ID 1289032.1)

八，impdp与dblink

有时候迁移数据时，由于文件系统的空间不足，可以使用impdp+network link的方式不落地迁移数据，

另外GoldenGate初始化数据时，可经常使用，但这种方式的缺点是不支持表中有long字段

ORA-31679: Table data object"xxx"."CS_APP_VER_CONTROL" has long columns, and longs cannot be loaded/unloaded using a network link

示例：

vi $ORACLE_HOME/network/admin/tnsnames.ora

xxx =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xx1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = xx2)(PORT = 1521))

    )(CONNECT_DATA =(SERVICE_NAME = xxxdb)     (SERVER = DEDICATED)

    )  )

create public database link xxxdb connectto goldengate identified by xxxxx using 'xxxdb';

[oracle@/home/oracle]$cat import.par

userid=goldengate/xxxxx

NETWORK_LINK=xxxdb

parallel=2

cluster=N

exclude=TABLE_STATISTICS,INDEX_STATISTICS,TRIGGER

FLASHBACK_SCN=10322009322328

TABLES=BMSOPER.EB_CATEGORY

remap_tablespace=TS_BMSIDX:TS_BMSOPER

nohup impdp parfile=import.par &

九、获取expdp导出文件的信息

在不知道expdp导出命令时，如何读取dump文件的信息（包括字符集，版本等），可以通过Oracle官方DBMS_DATAPUMP.GET_DUMPFILE_INFO或者show_dumpfile_info存储过程

PROCEDURE GET_DUMPFILE_INFO

 Argument Name                  Type                    In/Out Default?

 ------------------------------ ----------------------- ------ --------

 FILENAME                       VARCHAR2                IN

 DIRECTORY                      VARCHAR2                IN

 INFO_TABLE                    KU$_DUMPFILE_INFO       OUT

 FILETYPE                          NUMBER                  OUT

SET serveroutput on SIZE 1000000

exec show_dumpfile_info('expdp','full.dmp')

十、expdp导出文件最大数限制

导出文件使用%U变量，最大支持99个文件，如果指定了单个文件filesize大小，导出的文件总量是filesize*99，如果超出将出现以下报错

ORA - 39095 : " dump file space hasbeen exhausted. Unable to allocate 4096 bytes"

jobsystem.sys_export_full_02 stops due tofatal error.

 可以使用多个dump文件，或者设置更大的filesize参数

dumpfile=fullexp%U.dmp, fullexp2_%U.dmp,fullexp3_%U.dmp